<?php



class Model_Payroll_Staffbonus extends Zend_Db_Table_Abstract {

	protected $_name = 'staff_bonus';
	
	public function getStaffbonus( $idBonus){
		
		$row = $this->fetchRow("`id_bonus` = ". $idBonus);
		if(!$row) return false;
		return $row->toArray();		
		
		}
		
	public function getBonusByIdDate($idStaff,$dateBegin,$dateEnd){
		$row = $this->fetchRow("`id_staff` = ".$idStaff." AND `period_bonus_begin` = '{$dateBegin}' AND `Period_bonus_end` = '{$dateEnd}' AND `type_bonus` = 3");
		if(!$row) return false;
		return $row->toArray();

	}
		
	public function addStaffbonus( $idStaff , $typeBonus , $detailBonus , $amtTotalBonus , $amtCashBonus , $amtBankBonus , $amtBonusSuper , $periodBonusBegin , $periodBonusEnd , $payDate , $payType , $holdToDate , $paidBonus , $paidDate , $cancelBonus , $paidAmtCash , $paidAmtBank , $paidAmtSuper,$holdReason){
		
		$data = array(
			
         "id_staff" =>  $idStaff ,
         "type_bonus" =>  $typeBonus ,
         "detail_bonus" =>  $detailBonus ,
         "amt_total_bonus" =>  $amtTotalBonus ,
         "amt_cash_bonus" =>  $amtCashBonus ,
         "amt_bank_bonus" =>  $amtBankBonus ,
         "amt_bonus_super" =>  $amtBonusSuper ,
         "period_bonus_begin" =>  $periodBonusBegin ,
         "Period_bonus_end" =>  $periodBonusEnd ,
         "pay_date" =>  $payDate ,
         "pay_type" =>  $payType ,
         "hold_to_date" =>  $holdToDate ,
         "paid_bonus" =>  $paidBonus ,
         "paid_date" =>  $paidDate ,
         "cancel_bonus" =>  $cancelBonus ,
         "paid_amt_cash" =>  $paidAmtCash ,
         "paid_amt_bank" =>  $paidAmtBank ,
         "paid_amt_super" =>  $paidAmtSuper,
				"hold_reason" =>  $holdReason
				 
	
			);
		$this->insert($data);
		
		}
		
	public function newStaffbonus($idStaff , $typeBonus , $detailBonus , $amtTotalBonus , $amtCashBonus , $amtBankBonus , $amtBonusSuper , $periodBonusBegin , $periodBonusEnd , $payDate , $payType , $holdToDate,$holdReason,$shopCode = null){
		
		$data = array(
					
				"id_staff" =>  $idStaff ,
				"type_bonus" =>  $typeBonus ,
				"detail_bonus" =>  $detailBonus ,
				"amt_total_bonus" =>  $amtTotalBonus ,
				"amt_cash_bonus" =>  $amtCashBonus ,
				"amt_bank_bonus" =>  $amtBankBonus ,
				"amt_bonus_super" =>  $amtBonusSuper ,
				"period_bonus_begin" =>  $periodBonusBegin ,
				"Period_bonus_end" =>  $periodBonusEnd ,
				"pay_date" =>  $payDate ,
				"pay_type" =>  $payType ,
				"hold_to_date" =>  $holdToDate,
				"hold_reason" =>  $holdReason,
				"shop_code" => $shopCode
		
		);
		$this->insert($data);
				
		return $this->getAdapter()->lastInsertId();
	}

	public function changePayDate($idBonus,$payDate,$holdToDate){
		$data = array(
					
				"pay_date" =>  $payDate ,
				"hold_to_date" =>  $holdToDate
		);
			
		$this->update($data,"`id_bonus` = ". $idBonus);		
		
	}
	public function cancelBonus($idBonus,$comment = null){
		$data = array(
         "cancel_bonus" =>  Model_DatetimeHelper::dateToday(),
				"release_comment" => $comment
		);
			
		$this->update($data,"`id_bonus` = ". $idBonus);		
		
	}
		
	public function updateStaffbonus(  $idBonus ,  $idStaff , $typeBonus , $detailBonus , $amtTotalBonus , $amtCashBonus , $amtBankBonus , $amtBonusSuper , $periodBonusBegin , $periodBonusEnd , $payDate , $payType , $holdToDate , $paidBonus , $paidDate , $cancelBonus , $paidAmtCash , $paidAmtBank , $paidAmtSuper){
		$data = array(
			
         "id_staff" =>  $idStaff ,
         "type_bonus" =>  $typeBonus ,
         "detail_bonus" =>  $detailBonus ,
         "amt_total_bonus" =>  $amtTotalBonus ,
         "amt_cash_bonus" =>  $amtCashBonus ,
         "amt_bank_bonus" =>  $amtBankBonus ,
         "amt_bonus_super" =>  $amtBonusSuper ,
         "period_bonus_begin" =>  $periodBonusBegin ,
         "Period_bonus_end" =>  $periodBonusEnd ,
         "pay_date" =>  $payDate ,
         "pay_type" =>  $payType ,
         "hold_to_date" =>  $holdToDate ,
         "paid_bonus" =>  $paidBonus ,
         "paid_date" =>  $paidDate ,
         "cancel_bonus" =>  $cancelBonus ,
         "paid_amt_cash" =>  $paidAmtCash ,
         "paid_amt_bank" =>  $paidAmtBank ,
         "paid_amt_super" =>  $paidAmtSuper 
	
			);
			
		$this->update($data,"`id_bonus` = ". $idBonus);
		}
		
	public function deleteStaffbonus( $idBonus){
		
		$this->delete("`id_bonus` = ". $idBonus);
		
		}	
	
	public function listAll(){
		
		$rows =$this->fetchAll("1","id_bonus DESC ");
		if(!$rows) return false;
		return $rows->toArray();			
		
		}	
		
	public function listBonusByPayDate($dateBegin = null,$dateEnd = null){
		
		if($dateBegin==""){	
			$dateBegin = Model_DatetimeHelper::dateToday();
		}
		if($dateEnd==""){
			$dateEnd = Model_DatetimeHelper::dateToday();
		}
		
		$mBegin = Model_DatetimeHelper::getFirstDayOfTheMonth($dateBegin);
		$mEnd = Model_DatetimeHelper::getLastDayOfTheMonth($dateBegin);
		
		$whereStr = "`pay_date` >= '{$dateBegin}' AND `pay_date` <= '{$dateEnd}' AND `hold_to_date` = '0000-00-00' AND `cancel_bonus` IS NULL "; 
		
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();		
		
		
	}
	
	public function onceCode(){
		$whereStr = "`period_bonus_begin` >= '2014-04-28' AND `pay_type` =2 AND `calculate_status` LIKE 'yes' AND `pay_method_status` IS NULL AND `display_status` IS NULL";
		$rows = $this->fetchAll($whereStr);
		if(empty($rows)) return false;
		return $rows->toArray();
		
	}
	
	public function updateAmt($idBonus,$amtBank,$amtSuper){
		$data = array("amt_bank_bonus" => $amtBank,
					  "amt_bonus_super" => $amtSuper
				);
		$this->update($data,"`id_bonus` = ". $idBonus);
	}
	public function listReleasedBonus($idStaff,$date){
		
		$whereStr = "`id_staff` = {$idStaff} AND `calculate_status` = 'yes' AND `pay_method_status` = 'yes' AND `display_status` = 'yes' AND `type_bonus` < 4 AND `cancel_status` is null AND `display_date` <= '".$date."'";
		$rows =$this->fetchAll($whereStr,"pay_date ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
				
	}
	public function listAllHoldBonus(){
		
		$whereStr = "`type_bonus` < 4 AND `hold_to_date` != '0000-00-00' AND `cancel_bonus` IS NULL ";
		
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();		
		
		
	}
	
	public function bonusPaid($idBonus,$paidDate){
		$data = array(
				"paid_date" =>  $paidDate 		
		);
			
		$this->update($data,"`id_bonus` = ". $idBonus);
		
	}	
	
	public function getStockLostByShop($shopCode,$dateBegin,$dateEnd,$stkLostType = 4){
		$whereStr = "`shop_code` LIKE '{$shopCode}' AND `period_bonus_begin` = '{$dateBegin}' AND `period_bonus_end` = '{$dateEnd}' AND `type_bonus` = {$stkLostType}";
		$rows =$this->fetchRow($whereStr);
		if(!$rows) return false;
		return $rows->toArray();		
		
	}
	public function releaseBonus($idBonus,$payDate,$releaseComment){
		
		$data = array(
				"pay_date" =>  $payDate ,		
				"hold_to_date" =>  '0000-00-00' ,
				"release_comment" => $releaseComment						
				);
		$this->update($data,"`id_bonus` = ". $idBonus);	
	}
	
	public function findBonus($idStaff,$typeBonus,$amtTotal){
		$whereStr = "`id_staff` = {$idStaff} AND `type_bonus` = {$typeBonus} AND `amt_total_bonus` = {$amtTotal}";
		$rows = $this->fetchAll($whereStr);
		if(count($rows) > 1){
			
			echo "{idStaff} has 2 {$typeBonus}";
			
		}
		else{
			
			return $rows[0]['id_bonus'];
		}	
		return false;	
		
	}
	public function updateBankBonus($idBonus,$amtBk,$amtSp){
		$data = array(
				"amt_bank_bonus" =>  $amtBk ,
				"amt_bonus_super" =>  $amtSp ,
		);
		$this->update($data,"`id_bonus` = ". $idBonus);		
		
	}
	
	public function updateBonus($idBonus,$amtTotal,$amtBk,$amtSp){
		$data = array(
				"amt_total_bonus"=> $amtTotal,
				"amt_cash_bonus" => $amtTotal,
				"amt_bank_bonus" =>  $amtBk ,
				"amt_bonus_super" =>  $amtSp ,
		);
		$this->update($data,"`id_bonus` = ". $idBonus);
	
	}
	
	/**
	 * For update calculation status
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $displayDate
	 * @param unknown_type $payDate
	 */
	public function updateDisplayPayDate($idBonus, $displayDate,$payDate){
		$date = array(
			"display_date" => $displayDate,
			"pay_date"	=> $payDate,
			"calculate_status"	=> 'yes',
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * For update display status
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $displayDate
	 */
	public function updateDisplayStatusDate($idBonus, $displayDate){
		$date = array(
				"display_date" => $displayDate,
				
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * For update paymethod status
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $payDate
	 */
	public function updatePaymethodStatusDate($idBonus, $payDate){
		$date = array(
				"pay_date" => $payDate,
				
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * For update confirm paydate
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $confirmPayDate
	 */
	public function updateConfirmPaydate($idBonus, $confirmPayDate){
		$date = array(
				"confirm_paydate" => $confirmPayDate,
				"calculate_status"	=> 'yes',
				"pay_method_status"	=> 'yes',
				"display_status"	=> 'yes',
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * Update hold reason
	 * @param unknown_type $idBonus
	 * @param unknown_type $holdReason
	 */
	public function updateHoldReason($idBonus, $holdReason){
		$date = array(
				"hold_reason" => $holdReason,
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * For update payment status
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $paidDate
	 */
	public function updatePaymentStatus($idBonus, $paidDate,$authorized){
		$date = array(
				"paid_date" => $paidDate,
				"payment_status"	=> 'yes',
				"author_person" => $authorized,
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	public function backToHoldStatus($idBonus){
		$date = array(
				"pay_method_status" => null,
				"display_status"	=> null,
				"confirm_paydate" => null,
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	
	/**
	 * For update cancel status
	 * @author Leo
	 * @param unknown_type $idBonus
	 * @param unknown_type $cancelReason
	 */
	public function updateCancelStatus($idBonus, $cancelReason){
		$date = array(
				"paid_date" => '0000-00-00',
				"display_status"	=> 'yes',
				"pay_method_status"	=> 'yes',
				"payment_status" => 'yes',
				"cancel_status"	=> 'yes',
				"confirm_paydate" => '0000-00-00',
				"cancel_reason" => $cancelReason,	
		);
		$this->update($date,"`id_bonus` = ". $idBonus);
	}
	
	/**
	 * List all team bonus by cycle period
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 */
	public function listPeriodBonus($dateBegin,$dateEnd){
		$whereStr = "`period_bonus_begin` = '".$dateBegin."'"." AND `Period_bonus_end` = '".$dateEnd."' AND `type_bonus` = 3";
		
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	/**
	 * List all repair bonus by cycle period
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 */
	public function listPeriodRepairBonus($dateBegin,$dateEnd){
		$whereStr = "`period_bonus_begin` = '".$dateBegin."'"." AND `Period_bonus_end` = '".$dateEnd."' AND `type_bonus` = 1";
	
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	
	/**
	 * List all release bonus for accounting
	 * @author Leo
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 * @return boolean|multitype:
	 */
	public function listReleasePaymentStaff($dateBegin,$dateEnd){
		$whereStr = "`confirm_paydate` >= '".$dateBegin."'"." AND `confirm_paydate` <= '".$dateEnd."' AND `type_bonus` = 3 AND `calculate_status` like 'yes' AND `pay_method_status` like 'yes' AND `display_status` like 'yes' AND `cancel_status` is null OR 
					`confirm_paydate` >= '".$dateBegin."'"." AND `confirm_paydate` <= '".$dateEnd."' AND `type_bonus` = 1 AND `calculate_status` like 'yes' AND `pay_method_status` like 'yes' AND `display_status` like 'yes' AND `cancel_status` is null";
	
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	
	/**
	 * For export all released staff export to MYOB file 
	 * @param unknown_type $dateBegin
	 * @param unknown_type $dateEnd
	 */
	public function exportReleasedStaffMYOB($dateBegin,$dateEnd){
		$whereStr = "`confirm_paydate` >= '".$dateBegin."'"." AND `confirm_paydate` <= '".$dateEnd."' AND `type_bonus` = 3 AND `calculate_status` like 'yes' AND `pay_method_status` like 'yes' AND `display_status` like 'yes' AND `cancel_status` is null OR
		`confirm_paydate` >= '".$dateBegin."'"." AND `confirm_paydate` <= '".$dateEnd."' AND `type_bonus` = 1 AND `calculate_status` like 'yes' AND `pay_method_status` like 'yes' AND `display_status` like 'yes' AND `cancel_status` is null";
	
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	
	public function listAllUnpaidRBonusByStaffId($id){
		$whereStr = "`id_staff` = ".$id." AND `type_bonus` = 1 AND `cancel_status` is null AND `payment_status` is null";
		$rows =$this->fetchAll($whereStr,"id_staff ASC ");
		if(empty($rows)) return false;
		return $rows->toArray();
	}
	
}


?>